-module (mysql_utils).
-export ([select/4,
          select/5,
          select/6,
          select/7,
          select/8,
          delete/3,
          delete/4,
          delete/5,
          update/4,
          insert/3,
          insert/4,
          replace/4,
          make_select_sql/3,
          make_select_sql/4,
          make_select_sql/5,
          make_insert_sql/2,
          make_insert_sql/3,
          make_update_sql/3,
          make_delete_sql/2,
          make_delete_sql/3,
          make_delete_sql/4,
          get_where_sql/1
          ]).

-include_lib("emysql/include/emysql.hrl").

record_fields(Record_fields) ->
  {Result, _} = lists:foldl(
    fun(Item ,{AccIn, Sum}) ->
      Item2 = atom_to_list(Item),
        case Sum =:= 1 of
          true  -> {lists:concat([AccIn, Item2]), Sum};
          false -> {lists:concat([AccIn, Item2, ", "]), Sum-1}
        end
    end, {[], length(Record_fields)}, Record_fields),
  Result.

record_fields(Record_fields,TableName) ->
    {Result, _} = lists:foldl(
        fun(Item ,{AccIn, Sum}) ->
            Item2 = atom_to_list(Item),
            TableName1 = atom_to_list(TableName),
            TableName2 = [A || A <- TableName1,A < 48 orelse A > 57],
            case TableName2 ++ "_id" =:= Item2 of
              true ->
                {lists:concat([AccIn]), Sum-1};
              false ->
                case Sum =:= 1 of
                  true  -> {lists:concat([AccIn, Item2]), Sum};
                  false -> {lists:concat([AccIn, Item2, ", "]), Sum-1}
                end
            end
        end, {[], length(Record_fields)}, Record_fields),
    Result.

%% select(pool, table, "field1, field2, ...", [{field1, "=", 1, "or"}])
select(ConnPool, TableName, FieldsSql, WhereList)  ->
    select(ConnPool, TableName, FieldsSql, WhereList, []).

%% select(pool, table, "field1, field2, ...", [{field1, "=", 1, "or"}], [{field1, desc}])    
select(ConnPool, TableName, FieldsSql, WhereList, OrderList)when is_list(FieldsSql)  ->
    select(ConnPool, TableName, FieldsSql, WhereList, OrderList, []);

%% select(pool, table, recordname, record_info(fields, recordname), [{field1, "=", 1, "or"}])          
select(ConnPool, TableName, RecordName, RecordFields, WhereList) ->
     select(ConnPool, TableName, RecordName, RecordFields, WhereList, [], [], all).


%% select(pool, table, "field1, field2, ...", [{field1, "=", 1, "or"}], [{field1, desc}], [100])      
select(ConnPool, TableName, FieldsSql, WhereList, OrderList, LimitNum) when is_list(FieldsSql)  ->
    Sql = make_select_sql(TableName, FieldsSql, WhereList, OrderList, LimitNum),
    lager:debug("sql: ~ts~n", [Sql]),
    case emysql:execute(ConnPool, Sql) of
        #result_packet{rows = Rows} ->
            Rows;
        #error_packet{code =Code, msg = Msg} ->
            lager:debug("Code:~p, Msg: ~p~n", [Code, Msg]),
            {error,Code,Msg}
    end;
%% select(pool, table, recordname, record_info(fields, recordname), [{field1, "=", 1, "or"}], [{field1, desc}])  
select(ConnPool, TableName, RecordName, RecordFields, WhereList, OrderList) ->
     select(ConnPool, TableName, RecordName, RecordFields, WhereList, OrderList, [], all).

%% select(pool, table, recordname, record_info(fields, recordname), [{field1, "=", 1, "or"}], [{field1, desc}], [100])  
select(ConnPool, TableName, RecordName, RecordFields, WhereList, OrderList, LimitNum) ->
     select(ConnPool, TableName, RecordName, RecordFields, WhereList, OrderList, LimitNum, all).

%% select(pool, table, recordname, record_info(fields, recordname), [{field1, "=", 1, "or"}], [{field1, desc}], [100], all)
select(ConnPool, TableName, RecordName, RecordFields, WhereList, OrderList, LimitNum, Return) ->  
    Sql = make_select_sql(TableName, record_fields(RecordFields), WhereList, OrderList, LimitNum),
    lager:debug("sql: ~ts~n", [Sql]),
    case emysql:execute(ConnPool, Sql) of
        Result = #result_packet{} ->
            Returning = emysql_util:as_record(Result, RecordName, RecordFields),
            case Returning of
                [] -> case Return of
                        all -> [];
                        _   -> undefined
                    end;
                _  ->
                    case [Return, Returning] of
                        [_,   undefined]        -> undefined;
                        [all,         _]        -> Returning;
                        [first, [Return0| _T] ] -> Return0
                    end
            end;
        #error_packet{code =Code, msg = Msg} ->
            lager:debug("Code:~p, Msg: ~p~n", [Code, Msg]),
            {error,Code,Msg}
    end.

%% save(pool, table, [{field1, 1}, {field1, 1}])
insert(ConnPool, TableName, FieldValueList) ->
    Sql = make_insert_sql(TableName, FieldValueList),
    lager:debug("sql: ~ts~n", [Sql]),
    case emysql:execute(ConnPool, Sql) of
        #ok_packet{insert_id = ID} ->
            ID;
        #error_packet{code =Code, msg = Msg} ->
            lager:debug("Code:~p, Msg: ~p~n", [Code, Msg]),
            {error,Code,Msg}
    end.

%% save(pool, table, #record{},record_info(fields, recordname))
insert(ConnPool, TableName, Record, RecordFields) ->
    Sql = make_insert_sql(TableName, Record, RecordFields),
    lager:debug("sql: ~ts", [Sql]),
    case emysql:execute(ConnPool, Sql) of
        #ok_packet{insert_id = ID} ->
          ID;
        #error_packet{code =Code, msg = Msg} ->
          lager:debug("Code:~p, Msg: ~p~n", [Code, Msg]),
          {error,Code,Msg}
    end.

%% save(pool, table, #record{},record_info(fields, recordname))
replace(ConnPool, TableName, Record, RecordFields) ->
    Sql = make_replace_sql(TableName, Record, RecordFields),
    lager:debug("sql: ~ts", [Sql]),
    case emysql:execute(ConnPool, Sql) of
        #ok_packet{insert_id = ID} ->
          ID;
        #error_packet{code =Code, msg = Msg} ->
          lager:debug("Code:~p, Msg: ~p~n", [Code, Msg]),
          {error,Code,Msg}
    end.

%% update(pool, table, [{field1, 1}, {field2, 100}], [{field1, "=", 1, "or"}])
update(ConnPool, TableName, FieldValueList, WhereList) ->
    Sql = make_update_sql(TableName, FieldValueList, WhereList),
    lager:debug("sql: ~ts~n", [Sql]),
    case emysql:execute(ConnPool, Sql) of
        #ok_packet{affected_rows = Result} ->
            Result;
        #error_packet{code =Code, msg = Msg} ->
            lager:debug("Code:~p, Msg: ~p~n", [Code, Msg]),
            {error,Code,Msg}
    end.

%% delete(pool, table, [{field1, "=", 1, "or"}])
delete(ConnPool, TableName, WhereList) ->
    delete(ConnPool, TableName, WhereList, []).

%% delete(pool, table, [{field1, "=", 1, "or"}], [{field1, desc}])
delete(ConnPool, TableName, WhereList, OrderList) ->
    delete(ConnPool, TableName, WhereList, OrderList, []).

%% delete(pool, table, [{field1, "=", 1, "or"}], [{field1, desc}], [100])
delete(ConnPool, TableName, WhereList, OrderList, LimitNum) ->
    Sql = make_delete_sql(TableName, WhereList, OrderList, LimitNum),
    lager:debug("sql: ~ts~n", [Sql]),
    case emysql:execute(ConnPool, Sql) of
        #ok_packet{affected_rows = Result} ->
            Result;
        #error_packet{code =Code, msg = Msg} ->
            lager:debug("Code:~p, Msg: ~p~n", [Code, Msg]),
            {error,Code,Msg}
    end.

%%-------------------------------------------------------------------------------------------------------------------------
%%-------------------------------------------------------------------------------------------------------------------------
%%-------------------------------------------------------------------------------------------------------------------------
%%-------------------------------------------------------------------------------------------------------------------------
%%-------------------------------------------------------------------------------------------------------------------------
get_where_sql(WhereList) ->
    lists:mapfoldl(
      fun(Field_Operator_Val, Sum) -> 
          [Expr, Or_And_1] = 
            case Field_Operator_Val of   
              {Field, Operator, Val, Or_And} ->
                case is_binary(Val) orelse is_list(Val) of 
                  true -> try
                        [io_lib:format("~ts~ts~ts'~ts'",[Field," ",Operator, re:replace(Val,"'","'",[global,{return,binary}])]), Or_And]
                      catch
                        _:_ -> [lists:concat([Field," ",Operator," ","'",Val,"'"]), Or_And]
                      end;
                  _-> [io_lib:format("~ts~ts~ts'~p'",[Field," ",Operator, Val]), Or_And]
                end;
                        {_Field, in, []} ->
                            ["1=2","and"];
                        {Field, in, List} ->
                            case is_list(List) of
                                false -> [io_lib:format("`~s` in('~p')", [Field, List]),"and"];
                                true ->
                                    [_|Values] = lists:foldl(fun(L, Result) -> lists:concat([Result,",'",L,"'"]) end, "", List),
                                    [lists:concat([Field," in (",Values,")"]), "and"]
                            end;
              {Field, Operator, Val} ->
                case is_binary(Val) orelse is_list(Val) of 
                  true -> try 
                        [io_lib:format("~ts~ts~ts'~ts'",[Field, " ",Operator, re:replace(Val,"'","'",[global,{return,binary}])]), "and"]
                      catch
                        _:_ -> [lists:concat([Field," ",Operator," ","'",Val,"'"]),"and"]
                      end;                  
                  _-> [io_lib:format("~ts~ts~ts'~p'",[Field, " ",Operator, Val]),"and"]
                end;
              {Field, Val} ->  
                case is_binary(Val) orelse is_list(Val) of 
                  true -> try 
                        [io_lib:format("`~ts`='~ts'",[Field, re:replace(Val,"'","'",[global,{return,binary}])]), "and"]
                      catch
                        _:_ -> [io_lib:format("`~ts='~ts'",[Field, Val]), "and"]
                      end;                  
                  _-> [io_lib:format("`~ts`='~ts'",[Field, Val]), "and"]
                end;
               _-> ""
               end,  
          S1 = if Sum == length(WhereList) -> io_lib:format("~ts ",[Expr]);
              true -> io_lib:format("~ts ~s ",[Expr, Or_And_1])
             end,
          {S1, Sum+1}
        end,
    1, WhereList).  

get_limit_sql(LimitNum) ->
        case LimitNum of
            [] ->
                "";
            [Start, Num] ->
                lists:concat(["limit ", Start, ", ", Num]);
            [Num] ->
                lists:concat(["limit ", Num])
        end.

get_order_sql(OrderList) ->
    Len = length(OrderList),
    lists:mapfoldl(
      fun(Field_Order, Sum) ->  
              Expr = 
                  case Field_Order of   
                      {Field, Order} ->
                          io_lib:format("~p ~p",[Field, Order]);
                      {Field} ->
                          io_lib:format("~p",[Field])
                  end,
              S1 = if Sum == Len -> io_lib:format("~s ",[Expr]);
                      true ->   io_lib:format("~s,",[Expr])
                   end,
              {S1, Sum+1}
      end,
      1, OrderList). 

make_select_sql(TableName, FieldsSql, WhereList) ->
  make_select_sql(TableName, FieldsSql, WhereList, []).

make_select_sql(TableName, FieldsSql, WhereList, OrderList) ->
  make_select_sql(TableName, FieldsSql, WhereList, OrderList,[]).

make_select_sql(TableName, FieldsSql, WhereList, OrderList, LimitNum) ->
    %% db_mysqlutil:make_select_sql(player, "*", [{status, 1}], [{id,desc},{status}],[]).
    %% db_mysqlutil:make_select_sql(player, "id, status", [{id, 11}], [{id,desc},{status}],[]).
    {Wsql, Count1} = get_where_sql(WhereList),
    WhereSql = 
        if
            Count1 > 1 ->
                lists:concat(["where ", lists:flatten(Wsql)]);
            true ->
                ""
        end,
    {Osql, Count2} = get_order_sql(OrderList),
    OrderSql = 
        if
            Count2 > 1 ->
                lists:concat(["order by ", lists:flatten(Osql)]);
            true ->
                ""
        end,
    LimitSql =get_limit_sql(LimitNum),
    unicode:characters_to_binary(lists:concat(["select ", FieldsSql," from `", TableName, "` ", WhereSql, OrderSql, LimitSql, ""])).    

make_delete_sql(TableName, WhereList) ->
  make_delete_sql(TableName, WhereList, []).

make_delete_sql(TableName, WhereList, OrderList) ->
  make_delete_sql(TableName, WhereList, OrderList, []).

make_delete_sql(TableName, WhereList, OrderList, LimitNum) ->
    {Wsql, Count1} = get_where_sql(WhereList),
    WhereSql =
        if
            Count1 > 1 ->
                lists:concat(["where ", lists:flatten(Wsql)]);
            true -> 
                ""
        end,
    {Osql, Count2} = get_order_sql(OrderList),
    OrderSql = 
        if
            Count2 > 1->
                lists:concat(["order by ", lists:flatten(Osql)]);
            true -> 
                ""
        end,
    LimitSql = case LimitNum of
                   [] ->
                       "";
                   [Num] ->
                       lists:concat(["limit ", Num])
               end,
    unicode:characters_to_binary(lists:concat(["delete from `", TableName, "` ", WhereSql, OrderSql, LimitSql,";"])).    

make_update_sql(TableName, FieldValueList, WhereList) ->
    {Vsql, _} = lists:foldl(fun(Field_value ,{AccIn, Sum}) ->
                              Expr =
                                case Field_value of
                                  {Field, Val, add} when Val =/= undefined ->
                                    io_lib:format("`~s`=`~s`+~p", [Field, Field, Val]);
                                  {Field, Val, sub} ->
                                    io_lib:format("`~s`=`~s`-~p", [Field, Field, Val]);
                                  {Field, Val} when Val =:= undefined orelse Val =:= [] ->
                                    lists:concat([Field, "=", null]);
                                  {Field, Val} ->
                                    case is_binary(Val) orelse is_list(Val) of
                                      true ->
                                        lists:concat([Field, "=", "'",Val,"'"]);
                                      _->
                                        io_lib:format("`~s`='~p'",[Field, Val])
                                    end
                                end,
                              S1 = case Expr of
                                     [] ->
                                       [];
                                     _ ->
                                       io_lib:format("~ts ",[Expr])
                                   end,
                                case Expr of
                                [] ->
                                  {AccIn,Sum+1};
                                _ ->
                                  if
                                    Sum =:= 1  ->
                                      {lists:concat([AccIn,S1]), Sum+1};
                                    true ->
                                      {lists:concat([AccIn,",",S1]), Sum+1}
                                  end
                              end
                end,{[],1}, FieldValueList),
    {Wsql, Count2} = get_where_sql(WhereList),
    WhereSql = 
        if
            Count2 > 1 ->
                lists:concat(["where ", lists:flatten(Wsql)]);
            true ->
                ""
        end,
    unicode:characters_to_binary(lists:concat(["update `", TableName, "` set ",
                  lists:flatten(Vsql), WhereSql, ";"])).    

make_replace_sql(TableName, Record, RecordFields) ->
    [_ | Tail] = tuple_to_list(Record),
    [Id | Tail2] = Tail,
    {RecordFieldsSql, Tail3} = 
        case Id =/= undefined of
            true  -> {record_fields(RecordFields), Tail};
            false -> {record_fields(RecordFields, TableName), Tail2}
        end, 
    unicode:characters_to_binary(lists:concat(["insert into `", TableName, "`(", RecordFieldsSql ,") values(",make_fields_sql(Tail3), ")",";"])).

% phone 18008443771
make_insert_sql(TableName, FieldValueList) ->
    %%  db_mysqlutil:make_insert_sql(player, 
    %%                         [{status, 0}, {online_flag,1}, {hp,50}, {mp,30}]).
    Len = length(FieldValueList),
    {Vsql, _Count1} =
        lists:mapfoldl(
          fun(Field_value, Sum) ->      
                  Expr = 
                      case Field_value of
                          {Field, Val} -> 
                              lists:concat([Field, "=", "'",Val,"'"])
                      end,
                  S1 =
                      if
                          Sum == Len->
                              io_lib:format("~ts ", [Expr]);
                          true ->
                              io_lib:format("~ts,", [Expr])
                      end,
                  {S1, Sum+1}
          end,
          1, FieldValueList),
    unicode:characters_to_binary(lists:concat(["insert into `", TableName, "` set ", lists:flatten(Vsql),";"])).

make_insert_sql(TableName, Record, RecordFields) ->
    [_ | Tail] = tuple_to_list(Record),
    [Id | Tail2] = Tail,
    {RecordFieldsSql, Tail3} = 
        case Id =/= undefined of
            true  -> {record_fields(RecordFields), Tail};
            false -> {record_fields(RecordFields, TableName), Tail2}
        end, 
    unicode:characters_to_binary(lists:concat(["insert into `", TableName, "`(", RecordFieldsSql ,") values(",make_fields_sql(Tail3), ")",";"])).


make_fields_sql(List) ->
    {Result, _} = lists:foldl(
        fun(Item ,{AccIn, Sum}) ->
            % case Sum =:=  length(List) of
            %   true ->
            %       {lists:concat([AccIn]), Sum-1};
            %   false ->
                  
            % end

            Item2 = any_to_list(Item),
            Item3 = case is_integer(Item) orelse is_atom(Item) of
                true -> Item2;
                false -> 
					case Item =:= "" of 
						true -> lists:concat(["'", Item2 ,"'"]);
						false -> lists:concat(["'", Item2 ,"'"])
					end	
            end,
            case Sum =:= 1 of
                true  -> {lists:concat([AccIn, Item3]), Sum};
                false -> {lists:concat([AccIn, Item3, ", "]), Sum-1}
            end

        end, {[], length(List)}, List),
    Result.

any_to_list(V) when V =:= undefined ->
    null;
any_to_list(V) when is_float(V) ->
    hd(io_lib:format("~.6f",[V]));    
any_to_list(V) when is_list(V) ->
    V;
any_to_list(V) when is_atom(V) ->
    atom_to_list(V);
any_to_list(V) when is_binary(V) ->
    binary_to_list(V);
any_to_list(V) when is_integer(V) ->
    integer_to_list(V).
